from pathlib import Path
import scrapy
from scrapy.pipelines.images import ImagesPipeline
from scrapy.utils.project import get_project_settings
import pymysql


class DesigndbImgSpiderPipeline(ImagesPipeline):

    def get_media_requests(self, item, info):
        # print("get_media_requests：：：：：", item)
        if 'images' in item:
            # print('下载Imgae：', item['images'])
            for image_url in item['images']:
                # if image_url.find('data:image') > -1:
                # 将图片改成base64 Data
                # print("Base64图片：", image_url, type(image_url))
                # image_url_array = image_url.split(",")
                # image_url_array.sort(reverse=False)
                #
                # image_data = base64.b64decode(image_url[1])
                #
                # # 将二进制数据封装成文件流
                # image_stream = BytesIO(image_data)
                #
                # yield scrapy.Request(image_url, meta={'image_stream': image_stream})
                filename = self.get_media_requests(item, info)
                # print("item::::", item)
                # print("filename::::",filename)
                # print("download_url::::", image_url)
                yield scrapy.Request(image_url, meta={'item': item}, callback=self.parse_image, errback=self.errback)

    def errback(self, response):
        print("errback:::::", response)

    def parse_image(self, response):
        # print("response:::::", response)
        item = response
        yield item
        # item

    def file_path(self, request, response=None, info=None, *, item=None):
        # 自定义保存图片的路径
        # print("file_path：：：：：", item)

        # filePath = 'download/' + super().file_path(request, response=response, info=info)
        # # print("file_path：：：：：", filePath)
        # image_paths = item['image_paths']
        # image_paths.append(filePath)

        # print("image_paths：：：：：", image_paths)
        # return '../../../designdb_app/static/' + filePath
        # return '/static/' + filePath
        return super().file_path(request, response=response, info=info)

    def item_completed(self, results, item, info):
        # 可以在这里处理下载完成后的图片信息
        # image_paths = [x['path'] for ok, x in results if ok]

        image_paths = []
        for ok, x in results:
            if ok:
                if x is None:
                    continue
                for index, subitem in enumerate(list(x)):
                    print(index, item)
                    # temp = subitem['path'].replace("../../../designdb_app/", "");
                    image_paths.append(subitem['path'])
        item['image_paths'] = image_paths
        return item
        # return super().item_completed(results, item, info)


class DesigndbSpiderPipeline:
    pass


class SQLitePipeline:

    def __init__(self):
        self.settings = get_project_settings()  # 获取settings配置，设置需要的信息
        self.IMAGES_STORE = self.settings['IMAGES_STORE']
        self.host = self.settings['MYSQL_HOST']
        self.port = self.settings['MYSQL_PORT']
        self.user = self.settings['MYSQL_USER']
        self.passwd = self.settings['MYSQL_PASSWORD']
        self.db = self.settings['MYSQL_DATABASE']
        print(self.host, self.port, self.user, self.passwd, self.db, "图片下载路劲::", self.IMAGES_STORE)
        # 连接到具体的数据库（settings中设置的MYSQL_DBNAME）

    def connectDatabase(self):
        conn = pymysql.connect(host=self.host,
                               port=self.port,
                               user=self.user,
                               passwd=self.passwd,
                               db=self.db,
                               charset='utf8')  # 要指定编码，否则中文可能乱码
        return conn

    def open_spider(self, spider):
        # pass
        # db_name = spider.settings.get('SQLite_DATABASE')  # 这个方法获取不到数据
        # self.conn = sqlite3.connect('../../WIPO_Scrapy_DB.db')  # 打开或者创建数据库，库名在括号里
        self.conn = pymysql.connect(host=self.host,
                                    port=self.port,
                                    user=self.user,
                                    passwd=self.passwd,
                                    db=self.db,
                                    charset='utf8')
        # 连接MySQL

        self.cursor = self.conn.cursor()  # 获取游标

    # 关闭数据库
    def close_spider(self, spider):
        self.conn.close()  # 关闭数据库

    # 接收数据
    def process_item(self, item, spider):
        #
        # print("\nSQLitePipeline....process_item::::", item)
        # return item
        # 查询当前code是否存在
        lastrowid = 0
        QuerySql = (''' select * FROM wipo_Info WHERE Code='%s' ''' % item['code'])
        self.cursor.execute(QuerySql)
        result = self.cursor.fetchall()
        result_one = self.cursor.fetchall()
        # self.cursor.close()
        if result is not None and len(result) > 0:  # 更新
            self.cursor.execute(
                '''UPDATE wipo_Info SET `Source`=%s, `Holder`=%s, `RegTime`=%s, `ImgCout`=%s, `Code`=%s, `Type`=%s where `Code` =%s;''',
                (
                    item['source'],
                    item['holder'],
                    item['regtime'],
                    item['imgcout'],
                    item['code'],
                    item['type'],
                    result[0][1]
                ))
            self.conn.commit()  # 提交数据库操作
            # 获取
            lastrowid = result[0][0]

        else:  # 新增
            # 提取item数据
            print("InsertData::::", (
                item['name'],
                item['source'],
                item['holder'],
                item['regtime'],
                item['imgcout'],
                item['code'],
                item['type']
            ))
            self.cursor.execute(
                '''INSERT INTO wipo_Info (`Name`, `Source`, `Holder`, `RegTime`, `ImgCout`, `Code`, `Type`) VALUES (%s, %s, %s, %s, %s, %s, %s);''',
                (
                    item['name'],
                    item['source'],
                    item['holder'],
                    item['regtime'],
                    item['imgcout'],
                    item['code'],
                    item['type']
                ))

            self.conn.commit()  # 提交数据库操作
            lastrowid = self.cursor.lastrowid
            # 关闭游标
            # self.cursor.close()
            # 添加 图片地址
        print("imgcout::::" + str(item['imgcout']))
        if item['imgcout'] is not None and int(item['imgcout']) > 0:
            # pass
            # 先删除原来的
            # self.cursor.execute(f''' DELETE FROM wipo_imgs WHERE info_id={lastrowid}''')
            # self.cursor.execute("BEGIN")
            data = []
            for index, img_item in enumerate(list(item['image_paths'])):
                print(img_item)
                path = Path(f'{img_item}')
                # 得到名字
                data.append((lastrowid, path.name, item['images'][index], img_item, "", "", index))
            sql = '''INSERT INTO wipo_imgs (`info_id`, `ImageName`, `ImgUrl`, `ImgDownLoadUrl`, `Size`, `ImgFeature`, `Sort`) VALUES (%s, %s, %s, %s, %s, %s, %s);'''
            # self.cursor.execute(
            # sql,
            # (lastrowid,path.name,item['images'][index], img_item, "","",index))
            self.cursor.executemany(sql, data)
            self.conn.commit()

        # return item
